package com.shart.work.util;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Objects;

public class ExcelUtil {

    private static List data;
    private static Workbook workbook;
    private static Sheet sheet;
    private static String title;
    private static Map<String, CellStyle> cellStyleMap;
    private static Class<?> object;
    private static List<Object[]> fieldList;
    private static int rowNum;
    private static int importCellNum = 2;
    private static HttpServletResponse resp = getResp();

    private static HttpServletResponse getResp() {
        if (ObjectUtil.isNotEmpty(RequestContextHolder.getRequestAttributes())) {
            return ((ServletRequestAttributes) Objects.requireNonNull(RequestContextHolder.getRequestAttributes())).getResponse();
        }
        return null;
    }

    /**
     * 导出到本地磁盘
     *
     * @param objectClass 源class
     * @param dataList    数据集合
     * @param sheetName   sheet名称
     * @param title       表格标题
     * @param filePath    文件路径
     * @param <T>
     */
    public static <T> void excelExportLocalMachine(Class<?> objectClass, Collection<T> dataList, String sheetName, String title, String filePath) {
        if (dataList.isEmpty())
            dataList = Lists.newArrayList();
        fieldList = Lists.newArrayList();
        object = objectClass;
        data = (List) dataList;
        createWorkBook(sheetName, title);
        createCellStyle();
        createFields();
        createTitle();
        createHeader();
        createCellTable();
        writeFile(filePath);
    }

    /**
     * 导出自动下载
     *
     * @param objectClass
     * @param dataList
     * @param sheetName
     * @param title
     * @param fileName
     * @param <T>
     */
    public static <T> void excelExport(Class<?> objectClass, Collection<T> dataList, String sheetName, String title, String fileName) {
        if (dataList.isEmpty())
            dataList = Lists.newArrayList();
        fieldList = Lists.newArrayList();
        object = objectClass;
        data = (List) dataList;
        createWorkBook(sheetName, title);
        createCellStyle();
        createFields();
        createTitle();
        createHeader();
        createCellTable();
        download(fileName);
    }

    /**
     * 下载模板到本地磁盘
     *
     * @param objectClass 源class
     * @param sheetName   sheet名称
     * @param title       表格标题
     * @param <T>
     */
    public static <T> void dowlonadImportTemplateLocalMachine(Class<?> objectClass, String sheetName, String title, String path) {
        fieldList = Lists.newArrayList();
        object = objectClass;
        data = Lists.newArrayList();
        createWorkBook(sheetName, title);
        createCellStyle();
        createFields();
        createTitle();
        createHeader();
        createCellTable();
        writeFile(path);
    }

    /**
     * 下载模板
     *
     * @param objectClass 源class
     * @param sheetName   sheet名称
     * @param title       表格标题
     * @param <T>
     */
    public static <T> void dowlonadImportTemplate(Class<?> objectClass, String sheetName, String title, String fileName) {
        fieldList = Lists.newArrayList();
        object = objectClass;
        data = Lists.newArrayList();
        createWorkBook(sheetName, title);
        createCellStyle();
        createFields();
        createTitle();
        createHeader();
        createCellTable();
        download(fileName);
    }


    /**
     * 导入文件
     *
     * @param sheetName
     * @param inputStream
     * @param objectClass
     * @param <T>
     * @return
     */
    public static <T> List<T> excelImportLocalMachine(String sheetName, InputStream inputStream, Class<T> objectClass) {
        List<T> data = Lists.newArrayList();
        try {
            object = objectClass;
            fieldList = Lists.newArrayList();
            createFields();
            workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = StrUtil.isEmpty(sheetName) ? workbook.getSheetAt(0) : workbook.getSheet(sheetName);
            if (sheet == null) {
                throw new RuntimeException("sheet页不存在");
            }
            for (int i = importCellNum; i < sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                T object = ReflectUtil.newInstance(objectClass);
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Field field = (Field) fieldList.get(j)[0];
                    Cell cell = row.getCell(j);
                    Method method = object.getClass().getMethod(parSetName(field.getName()), Class.forName(valType(field, cell.getStringCellValue())[0].toString()));
                    method.invoke(object, valType(field, cell.getStringCellValue())[1]);
                }
                data.add(object);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return data;
    }

    /**
     * 转换类型
     *
     * @param field
     * @param val
     * @return
     */
    private static Object[] valType(Field field, String val) {
        if (StrUtil.equals("java.lang.Long", field.getType().getTypeName())) {
            return new Object[]{"java.lang.Long", Long.valueOf(val)};
        } else if (StrUtil.equals("java.lang.String", field.getType().getTypeName())) {
            return new Object[]{"java.lang.String", String.valueOf(val)};
        } else if (StrUtil.equals("java.lang.Integer", field.getType().getTypeName())) {
            return new Object[]{"java.lang.Integer", Integer.valueOf(val)};
        } else if (StrUtil.equals("java.lang.Double", field.getType().getTypeName())) {
            return new Object[]{"java.lang.Double", Double.valueOf(val)};
        }
        return null;
    }

    private static String parSetName(String fieldName) {
        return "set" + fieldName.substring(0, 1).toUpperCase()
                + fieldName.substring(1);
    }

    /**
     * 初始化工作本
     *
     * @param sheetName
     * @param tableTitle
     */
    private static void createWorkBook(String sheetName, String tableTitle) {
        workbook = new SXSSFWorkbook(600);
        sheet = workbook.createSheet(sheetName);
        title = tableTitle;
    }

    /**
     * 初始化样式
     */
    private static void createCellStyle() {
        cellStyleMap = Maps.newHashMap();
        // 1. 标题
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontName(HSSFFont.FONT_ARIAL);
        titleFont.setFontHeightInPoints((short) 18);
        titleFont.setBold(true);
        titleStyle.setFont(titleFont);
        cellStyleMap.put(Constr.TITLE_STYLE, titleStyle);
        // 2. 头
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());
        headerStyle.setBorderTop(BorderStyle.MEDIUM_DASHED);
        headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleMap.put(Constr.HEADER_STYLE, headerStyle);
        // 3. 数据集
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        dataStyle.setBorderBottom(BorderStyle.THICK);
        dataStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        dataStyle.setBorderLeft(BorderStyle.THICK);
        dataStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        dataStyle.setBorderRight(BorderStyle.THICK);
        dataStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        dataStyle.setBorderTop(BorderStyle.THICK);
        dataStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        dataStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
        dataStyle.setFillPattern(FillPatternType.FINE_DOTS);
        dataStyle.setWrapText(true);
        cellStyleMap.put(Constr.DATA_STYLE, dataStyle);
    }

    /**
     * 创建表格标题 居中
     */
    private static void createTitle() {
        Row row = sheet.createRow(rowNum == 0 ? rowNum++ : rowNum);
        Cell cell = row.createCell(0);
        cell.setCellValue(title);
        cell.setCellStyle(cellStyleMap.get(Constr.TITLE_STYLE));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
                fieldList.size() - 1));
    }

    /**
     * 创建标题
     */
    private static void createHeader() {
        Row row = sheet.createRow(rowNum == 0 ? rowNum++ : rowNum);
        for (int i = 0; i < fieldList.size(); i++) {
            Excel excel = (Excel) fieldList.get(i)[1];
            Cell cell = row.createCell(i);
            cell.setCellValue(excel.titleName());
            cell.setCellStyle(cellStyleMap.get(Constr.HEADER_STYLE));
        }

    }

    /**
     * 创建表格
     */
    private static <T> void createCellTable() {
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(++rowNum);
            T dataObject = (T) data.get(i);
            for (int j = 0; j < fieldList.size(); j++) {
                Field field = (Field) fieldList.get(j)[0];
                Excel excel = (Excel) fieldList.get(j)[1];
                Cell cell = row.createCell(j);
                String cellVal = String.valueOf(ReflectUtil.getFieldValue(dataObject, field));
                cell.setCellValue(cellVal);
                //  1. 字典数据转换
                if (StrUtil.isNotEmpty(excel.dictType())) {
                    cell.setCellValue(convertDictType(excel, cellVal));
                }
                cell.setCellStyle(cellStyleMap.get(Constr.DATA_STYLE));
            }

        }

    }

    /**
     * 字典转换数据 TODO 正式环境中请使用sql查询数据库字典
     *
     * @param excel
     * @param cellVal
     * @return
     */
    private static String convertDictType(Excel excel, String cellVal) {
        if (excel.dictType().equals(DictType.USER_STATUS)) {
            if (cellVal.equals("0")) {
                return "拒绝";
            } else if (cellVal.equals("1")) {
                return "同意";
            }
        }
        return "驳回";
    }


    /**
     * 获取标有注解的字段
     */
    private static void createFields() {
        Field[] fields = object.getDeclaredFields();
        for (Field field : fields) {
            Excel excel = field.getDeclaredAnnotation(Excel.class);
            if (excel != null && StrUtil.containsAnyIgnoreCase(excel.type(), "导出", "导入")) {
                field.setAccessible(true);
                fieldList.add(new Object[]{field, excel});
            }
        }
    }

    private static void writeFile(String path) {
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(path);
            workbook.write(fileOutputStream);
            fileOutputStream.close();

        } catch (IOException e) {

        }
    }

    private static void download(String fileName) {
        try {
            resp.addHeader("Content-disposition",
                    String.format("attachment;filename=%s.xls", new String(fileName.getBytes("gbk"), "iso8859-1")));
            // 定义输出类型
            resp.setContentType("octets/stream;rset=UTF-8");
            // 创建输出对象
            ServletOutputStream out = resp.getOutputStream();
            // 工作簿输出
            workbook.write(out);
            // 关闭输出流
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
